﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace ReadPointsRingNumberExcel
{
    public class wisenFileProcess
    {
        private static HSSFWorkbook hssfworkbook;

        public static void FileProcess(string path,string savePath)
        {
            using (FileStream file = new FileStream(path, FileMode.Open))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            var items = wisenFileProcess.GoThroughWorkBook(hssfworkbook);
            wisenFileProcess.SaveToExcel(savePath, items);
        }

        private static bool IsSheetNameValid(string name)
        {
            var test1 = new Regex(@"[\u4E00-\u9FA5]+\w*");
            var result1 = test1.IsMatch(name);
            if (result1 == false) return false;
            else if (name.IndexOf("网关") > -1 || name.IndexOf("裂缝") > -1 || name.IndexOf("应变") > -1)
                return false;
            else return true;
        }

        private static List<WisenItem> GoThroughWorkBook(HSSFWorkbook book)
        {
            var length = book.NumberOfSheets;
            var items = new List<WisenItem>();
            for (var i = 0; i < length; i++)
            {
                var name = book.GetSheetName(i);
                if (wisenFileProcess.IsSheetNameValid(name))
                {
                    var sheet = book.GetSheetAt(i);
                    var item = wisenFileProcess.ReadOneSheet(sheet);
                    if (item != null)
                    {
                        items.Add(item);
                    }
                }
            }
            return items;
        }
        private static void SaveToExcel(string path, List<WisenItem> items)
        {
            using (FileStream file = new FileStream(path, FileMode.Create))
            {
                var sheet = hssfworkbook.CreateSheet("汇总");

                for (var i = 0; i <= 8; i++)
                {
                    var width = i == 0 ? 40 : 14;
                    sheet.SetColumnWidth(i, width * 256);
                }
                var index = hssfworkbook.GetSheetIndex("汇总");
                hssfworkbook.SetActiveSheet(index);
                var cellHeader = sheet.GetCell(0, 0, true);
                cellHeader.SetCellValue("名称");
                cellHeader.Offset(0, 1).SetCellValue("日期");
                cellHeader.Offset(0, 2).SetCellValue("时间");
                cellHeader.Offset(0, 3).SetCellValue("电压1");
                cellHeader.Offset(0, 4).SetCellValue("电压2");
                cellHeader.Offset(0, 5).SetCellValue("温度");
                cellHeader.Offset(0, 6).SetCellValue("湿度");
                cellHeader.Offset(0, 7).SetCellValue("倾角a");
                cellHeader.Offset(0, 8).SetCellValue("倾角b");
                for (int i = 0; i < items.Count; i++)
                {
                    //日期	时间	电压1	电压2	温度	湿度	倾角a	倾角b
                    var item = items[i];
                    var cell = sheet.GetCell(i + 1, 0, true);
                    cell.SetCellValue(item.Name);
                    cell.Offset(0, 1).SetCellValue(item.Date);
                    cell.Offset(0, 2).SetCellValue(item.Time);
                    cell.Offset(0, 3).SetCellValue(item.Voltage1);
                    cell.Offset(0, 4).SetCellValue(item.Voltage2);
                    cell.Offset(0, 5).SetCellValue(item.Temperature);
                    cell.Offset(0, 6).SetCellValue(item.Humidity);
                    cell.Offset(0, 7).SetCellValue(item.Angle1);
                    cell.Offset(0, 8).SetCellValue(item.Angle2);
                    cell.Row.Height = 200 * 2;
                }
                sheet.Workbook.Write(file);
            }
        }

        private static WisenItem ReadOneSheet(ISheet sheet)
        {
            var startRow = 1;
            var items = new List<WisenItem>();
            do
            {
                var time = sheet.GetCellText(startRow,1);
                if (time == "") break;
                if (wisenFileProcess.IsTimeValid(time))
                {
                    var item = new WisenItem()
                    {
                        //日期	时间	电压1	电压2	温度	湿度	倾角a	倾角b
                        Date = sheet.GetCellText(startRow, 0),
                        Voltage1 = double.Parse(sheet.GetCellText(startRow, 2)),
                        Voltage2 = double.Parse(sheet.GetCellText(startRow, 3)),
                        Temperature = double.Parse(sheet.GetCellText(startRow, 4)),
                        Humidity = double.Parse(sheet.GetCellText(startRow, 5)),
                        Angle1 = double.Parse(sheet.GetCellText(startRow, 6)),
                        Angle2 = double.Parse(sheet.GetCellText(startRow, 7))
                    };
                    items.Add(item);
                }
                startRow = startRow + 1;
            } while (true);
            if (items.Count > 0)
            {
                var summaryItem = new WisenItem()
                {
                    Name = sheet.SheetName,
                    Time = "03:00:00",
                    Date = items[0].Date,
                    Voltage1 = items.Average(p => p.Voltage1),
                    Voltage2 = items.Average(p => p.Voltage2),
                    Temperature = items.Average(p => p.Temperature),
                    Humidity = items.Average(p => p.Humidity),
                    Angle1 = items.Average(p => p.Angle1),
                    Angle2 = items.Average(p => p.Angle2),
                };
                return summaryItem;
            }
            else { return null; }
        }

        private static bool IsTimeValid(string time)
        {
            if(time.Length==0)return false;
            var dateT = DateTime.Parse(time);
            if (dateT.Hour >= 3 && dateT.Hour < 4)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

    }
    public class WisenItem
    {
        //日期	时间	电压1	电压2	温度	湿度	倾角a	倾角b
        public string Name { get; set; }
        public string Date { get; set; }
        public string Time { get; set; }
        public double Voltage1 { get; set; }
        public double Voltage2 { get; set; }
        public double Humidity { get; set; }
        public double Temperature { get; set; }
        public double Angle1 { get; set; }
        public double Angle2 { get; set; }

        public WisenItem()
        {
            this.Time = "03:00:00";
        }
    }
}
